package com.sdgakj.repository;

import com.sdgakj.entity.ShtProvince;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;

import java.util.List;

public interface ShtProvinceDao extends PagingAndSortingRepository<ShtProvince, Long>, JpaSpecificationExecutor<ShtProvince>
{
    @Query(value = "select * from sht_provinces where id=?1", nativeQuery = true)
    ShtProvince getProvinceById(Long id);
    
    @Query(value = "select * from sht_provinces order by convert(name using gbk)", nativeQuery = true)
    List<ShtProvince> getProvinceOrderbyName();
    
    /**已有的检测场所属省份**/
    @Query(value="select p.* from sht_provinces p where p.id in (select province_id from jcc_detect_factory )", nativeQuery = true)
    List<ShtProvince> findProvince4Factory();

    /********************below use for manager  interface************************/
    /**已有的检测场所属省份**/
    @Query(value="select province_id from jcc_detect_factory", nativeQuery = true)
    List<Integer> findProvinceIds4Factory();
}
